package com.thinkgem.jeesite.common.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel
{
  public static List<List<Object>> readExcel(File file)
    throws IOException
  {
    String fileName = file.getName();
    String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
    if ("xls".equals(extension)) {
      return read2003Excel(file);
    }
    if ("xlsx".equals(extension)) {
      return read2007Excel(file);
    }
    throw new IOException("不支持的文件类型");
  }
  
  private static List<List<Object>> read2003Excel(File file)
    throws IOException
  {
    List<List<Object>> list = new LinkedList();
    HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
    HSSFSheet sheet = hwb.getSheetAt(0);
    Object value = null;
    HSSFRow row = null;
    HSSFCell cell = null;
    int counter = 0;
    for (int i = sheet.getFirstRowNum(); counter < sheet.getPhysicalNumberOfRows(); i++)
    {
      row = sheet.getRow(i);
      if (row != null)
      {
        counter++;
        
        List<Object> linked = new LinkedList();
        for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++)
        {
          cell = row.getCell(j);
          if (cell != null)
          {
            DecimalFormat df = new DecimalFormat("0");
            
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            DecimalFormat nf = new DecimalFormat("0.00");
            switch (cell.getCellType())
            {
            case 1: 
              value = cell.getStringCellValue();
              break;
            case 0: 
              if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                value = df.format(cell.getNumericCellValue());
              } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                value = nf.format(cell.getNumericCellValue());
              } else {
                value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
              }
              break;
            case 4: 
              value = Boolean.valueOf(cell.getBooleanCellValue());
              break;
            case 3: 
              value = "";
              break;
            case 2: 
            default: 
              value = cell.toString();
            }
            if ((value != null) && (!"".equals(value))) {
              linked.add(value);
            }
          }
        }
        list.add(linked);
      }
    }
    return list;
  }
  
  private static List<List<Object>> read2007Excel(File file)
    throws IOException
  {
    List<List<Object>> list = new LinkedList();
    
    XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
    
    XSSFSheet sheet = xwb.getSheetAt(0);
    Object value = null;
    XSSFRow row = null;
    XSSFCell cell = null;
    int counter = 0;
    for (int i = sheet.getFirstRowNum(); counter < sheet.getPhysicalNumberOfRows(); i++)
    {
      row = sheet.getRow(i);
      if (row != null)
      {
        counter++;
        
        List<Object> linked = new LinkedList();
        for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++)
        {
          cell = row.getCell(j);
          if (cell != null)
          {
            DecimalFormat df = new DecimalFormat("0");
            
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            DecimalFormat nf = new DecimalFormat("0.00");
            switch (cell.getCellType())
            {
            case 1: 
              value = cell.getStringCellValue();
              break;
            case 0: 
              if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                value = df.format(cell.getNumericCellValue());
              } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                value = nf.format(cell.getNumericCellValue());
              } else {
                value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
              }
              break;
            case 4: 
              value = Boolean.valueOf(cell.getBooleanCellValue());
              break;
            case 3: 
              value = "";
              break;
            case 2: 
            default: 
              value = cell.toString();
            }
            if ((value != null) && (!"".equals(value))) {
              linked.add(value);
            }
          }
        }
        list.add(linked);
      }
    }
    return list;
  }
}
